package com.education.ai.dao.impl;

import com.education.ai.dao.TeacherDao;
import com.education.ai.entity.Teacher;
import com.education.ai.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.sql.Types;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;
import java.util.Date;

/**
 * 教师数据访问实现类
 */
@Repository
public class TeacherDaoImpl implements TeacherDao {

    private static final Logger logger = LoggerFactory.getLogger(TeacherDaoImpl.class);
    
    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 查询所有教师
     */
    @Override
    public List<Teacher> findAll() {
        String sql = "SELECT * FROM teachers";
        return mapResultsToTeachers(jdbcUtil.executeQuery(sql));
    }
    
    /**
     * 根据ID查询教师
     */
    @Override
    public Optional<Teacher> findById(Long id) {
        String sql = "SELECT * FROM teachers WHERE id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, id);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToTeacher(results.get(0)));
    }
    
    /**
     * 根据教师ID查询教师
     */
    @Override
    public Optional<Teacher> findByTeacherId(String teacherId) {
        String sql = "SELECT * FROM teachers WHERE teacher_id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, teacherId);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToTeacher(results.get(0)));
    }
    
    /**
     * 根据用户名查询教师（使用教师工号作为用户名）
     */
    @Override
    public Optional<Teacher> findByUsername(String username) {
        // 在教师表中，通常使用teacher_id作为用户名
        return findByTeacherId(username);
    }
    
    /**
     * 根据教师邮箱查询
     */
    public Optional<Teacher> findByEmail(String email) {
        String sql = "SELECT * FROM teachers WHERE email = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, email);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToTeacher(results.get(0)));
    }
    
    /**
     * 根据教师姓名模糊查询
     */
    public List<Teacher> findByNameContaining(String name) {
        String sql = "SELECT * FROM teachers WHERE name LIKE ?";
        return mapResultsToTeachers(jdbcUtil.executeQuery(sql, "%" + name + "%"));
    }
    
    /**
     * 根据学科查找教师
     */
    public List<Teacher> findBySubject(String subject) {
        String sql = "SELECT * FROM teachers WHERE subject = ?";
        return mapResultsToTeachers(jdbcUtil.executeQuery(sql, subject));
    }
    
    /**
     * 根据学科和姓名查找教师
     */
    public List<Teacher> findBySubjectAndNameContaining(String subject, String name) {
        String sql = "SELECT * FROM teachers WHERE subject = ? AND name LIKE ?";
        return mapResultsToTeachers(jdbcUtil.executeQuery(sql, subject, "%" + name + "%"));
    }
    
    /**
     * 根据手机号查询教师
     */
    public Optional<Teacher> findByPhone(String phone) {
        String sql = "SELECT * FROM teachers WHERE phone = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, phone);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToTeacher(results.get(0)));
    }
    
    /**
     * 判断教师工号是否存在
     */
    public boolean existsByTeacherId(String teacherId) {
        String sql = "SELECT COUNT(*) AS count FROM teachers WHERE teacher_id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, teacherId);
        return !results.isEmpty() && ((Number) results.get(0).get("count")).intValue() > 0;
    }
    
    /**
     * 判断邮箱是否存在
     */
    public boolean existsByEmail(String email) {
        String sql = "SELECT COUNT(*) AS count FROM teachers WHERE email = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, email);
        return !results.isEmpty() && ((Number) results.get(0).get("count")).intValue() > 0;
    }
    
    /**
     * 判断手机号是否存在
     */
    public boolean existsByPhone(String phone) {
        String sql = "SELECT COUNT(*) AS count FROM teachers WHERE phone = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, phone);
        return !results.isEmpty() && ((Number) results.get(0).get("count")).intValue() > 0;
    }
    
    /**
     * 保存教师
     */
    @Override
    public Teacher save(Teacher teacher) {
        logger.info("开始保存教师: {}", teacher);
        
        if (teacher.getId() == null) {
            // 检查teacherId是否为null
            if (teacher.getTeacherId() == null || teacher.getTeacherId().trim().isEmpty()) {
                logger.error("保存教师失败: 教师工号(teacher_id)不能为空");
                throw new IllegalArgumentException("教师工号(teacher_id)不能为空");
            }
            
            // 插入操作
            String sql = "INSERT INTO teachers (name, teacher_id, password, email, phone, subject, profile, status, last_login_time, create_time, update_time) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            // 设置创建和更新时间
            LocalDateTime now = LocalDateTime.now();
            if (teacher.getCreateTime() == null) {
                teacher.setCreateTime(now);
            }
            if (teacher.getUpdateTime() == null) {
                teacher.setUpdateTime(now);
            }
            
            // 确保lastLoginTime不是空字符串而是null
            if (teacher.getLastLoginTime() != null && teacher.getLastLoginTime().toString().isEmpty()) {
                teacher.setLastLoginTime(null);
            }
            
            // 记录所有参数值
            logger.info("执行插入SQL: {}", sql);
            logger.info("SQL参数: name={}, teacher_id={}, password={}, email={}, phone={}, subject={}, profile={}, status={}, last_login_time={}, create_time={}, update_time={}",
                    teacher.getName(),
                    teacher.getTeacherId(),
                    "******", // 不记录密码
                    teacher.getEmail(),
                    teacher.getPhone(),
                    teacher.getSubject(),
                    teacher.getProfile(),
                    teacher.getStatus(),
                    teacher.getLastLoginTime(),
                    teacher.getCreateTime(),
                    teacher.getUpdateTime());
            
            long id = jdbcUtil.executeInsert(sql,
                    teacher.getName(),
                    teacher.getTeacherId(),
                    teacher.getPassword(),
                    teacher.getEmail(),
                    teacher.getPhone(),
                    teacher.getSubject(),
                    teacher.getProfile(),
                    teacher.getStatus(),
                    teacher.getLastLoginTime() != null ? Timestamp.valueOf(teacher.getLastLoginTime()) : null,
                    Timestamp.valueOf(teacher.getCreateTime()),
                    Timestamp.valueOf(teacher.getUpdateTime()));
            
            if (id <= 0) {
                logger.error("插入教师记录失败，未获取到有效ID");
                throw new RuntimeException("插入教师记录失败，未获取到有效ID");
            }
            
            teacher.setId(id);
            logger.info("成功插入教师记录，ID: {}", id);
            return teacher;
        } else {
            // 更新操作
            logger.info("执行更新操作，教师ID: {}", teacher.getId());
            update(teacher);
            return teacher;
        }
    }
    
    /**
     * 更新教师
     */
    @Override
    public boolean update(Teacher teacher) {
        String sql = "UPDATE teachers SET name = ?, password = ?, email = ?, phone = ?, " +
                "subject = ?, profile = ?, status = ?, last_login_time = ?, update_time = ? " +
                "WHERE id = ?";
        
        // 设置更新时间
        teacher.setUpdateTime(LocalDateTime.now());
        
        // 确保lastLoginTime不是空字符串而是null
        if (teacher.getLastLoginTime() != null && teacher.getLastLoginTime().toString().isEmpty()) {
            teacher.setLastLoginTime(null);
        }
        
        int rows = jdbcUtil.executeUpdate(sql,
                teacher.getName(),
                teacher.getPassword(),
                teacher.getEmail(),
                teacher.getPhone(),
                teacher.getSubject(),
                teacher.getProfile(),
                teacher.getStatus(),
                teacher.getLastLoginTime() != null ? Timestamp.valueOf(teacher.getLastLoginTime()) : null,
                Timestamp.valueOf(teacher.getUpdateTime()),
                teacher.getId());
        
        return rows > 0;
    }
    
    /**
     * 删除教师
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = "DELETE FROM teachers WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, id);
        return rows > 0;
    }
    
    /**
     * 查询总数
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) AS count FROM teachers";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    /**
     * 获取当前最大的教师工号
     */
    @Override
    public String getMaxTeacherId() {
        String sql = "SELECT MAX(teacher_id) AS max_id FROM teachers";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty() && results.get(0).get("max_id") != null) {
            return results.get(0).get("max_id").toString();
        }
        return null;
    }
    
    /**
     * 将查询结果映射为教师对象列表
     */
    private List<Teacher> mapResultsToTeachers(List<Map<String, Object>> results) {
        List<Teacher> teachers = new ArrayList<>();
        for (Map<String, Object> row : results) {
            teachers.add(mapRowToTeacher(row));
        }
        return teachers;
    }
    
    /**
     * 将单行结果映射为教师对象
     */
    private Teacher mapRowToTeacher(Map<String, Object> row) {
        Teacher teacher = new Teacher();
        
        teacher.setId(((Number) row.get("id")).longValue());
        teacher.setTeacherId((String) row.get("teacher_id"));
        teacher.setName((String) row.get("name"));
        teacher.setPassword((String) row.get("password"));
        teacher.setEmail((String) row.get("email"));
        teacher.setPhone((String) row.get("phone"));
        teacher.setSubject((String) row.get("subject"));
        teacher.setProfile((String) row.get("profile"));
        teacher.setStatus(row.get("status") != null ? ((Number) row.get("status")).intValue() : 1);
        
        // 处理时间字段
        if (row.get("last_login_time") != null) {
            teacher.setLastLoginTime(convertToLocalDateTime(row.get("last_login_time")));
        }
        
        if (row.get("create_time") != null) {
            teacher.setCreateTime(convertToLocalDateTime(row.get("create_time")));
        }
        
        if (row.get("update_time") != null) {
            teacher.setUpdateTime(convertToLocalDateTime(row.get("update_time")));
        }
        
        return teacher;
    }
    
    /**
     * 转换各种日期类型为LocalDateTime
     */
    private LocalDateTime convertToLocalDateTime(Object timeObj) {
        if (timeObj instanceof Date) {
            return Instant.ofEpochMilli(((Date) timeObj).getTime())
                    .atZone(ZoneId.systemDefault())
                    .toLocalDateTime();
        } else if (timeObj instanceof java.sql.Timestamp) {
            return ((java.sql.Timestamp) timeObj).toLocalDateTime();
        } else if (timeObj instanceof java.sql.Date) {
            return Instant.ofEpochMilli(((java.sql.Date) timeObj).getTime())
                    .atZone(ZoneId.systemDefault())
                    .toLocalDateTime();
        } else if (timeObj instanceof LocalDateTime) {
            return (LocalDateTime) timeObj;
        } else {
            logger.warn("无法识别的时间类型: {}, 值: {}", 
                    timeObj != null ? timeObj.getClass().getName() : "null", 
                    timeObj);
            return null;
        }
    }
} 